﻿/************************************************************************************
* Copyright (c) 2017 All Rights Reserved.
*命名空间：
*文件名： MsSqlHelper
*创建人： Sitaier
*创建时间：2017-01-01 00:00:00
*描述
*=====================================================================
*修改标记
*修改时间：2017-01-01 00:00:00
*修改人：Sitaier
*描述：
************************************************************************************/
using System;  
using System.Collections.Generic;
using System.Text;  
using System.Collections;  
using System.Data;  
using System.Data.SqlClient;  
using System.Configuration;

namespace KSOAPlus
{
    public class MsSqlHelper
    {
        #region 通用方法
        // 数据连接池  
        private SqlConnection con;
        /// <summary>  
        /// 返回数据库连接字符串  
        /// </summary>  
        /// <returns></returns>  
        public String GetSqlConnection()
        {
            //String conn = ConfigurationManager.AppSettings["connectionString"].ToString();
            String conn = ConfigurationManager.ConnectionStrings["mssqlConnectionString"].ToString();

            return conn;
        }

        #endregion

        /// <summary>
        /// 本地数据库连接是否成功
        /// </summary>
        /// <returns></returns>
        public bool connectTest()
        {
            bool result = false;
            //获取数据库连接字符串
            String ConnStr = GetSqlConnection();
            //创建连接对象
            SqlConnection conn = new SqlConnection(ConnStr);
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    result = true;
                }
            }
            catch
            {
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //SqlCommand myCmd = new SqlCommand("select * from sys.databases where name='SZY'", myCon);
        /// <summary>
        /// 检查数据库是否存在
        /// </summary>
        /// <param name="DataBase">数据库名</param>
        /// <returns></returns>
        public bool IsDataBase(string DataBase)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select count(*) from sys.databases where name='" + DataBase + "'";
                conn.Open();
                //int n = cmd.ExecuteNonQuery();
                int n = Convert.ToInt32(cmd.ExecuteScalar());
               // int n = int.Parse(cmd.ExecuteScalar().ToString());
                if (n > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public string IsDataBasex(string DataBase)
        {
            //String Sqlstr = "select * from sys.databases where name='" + DataBase + "'";
            String Sqlstr = "select count(*) from Tupian.dbo.gsetup";
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                //object n = cmd.ExecuteScalar();
                //int n = int.Parse(cmd.ExecuteScalar().ToString());
                int n = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
                if (n > 0)
                {
                    return n.ToString() + Sqlstr;
                }
                else
                {
                    return n.ToString() + Sqlstr;
                }
            }
        }
        public bool IsTable(string DataBase, string Table)
        {
            String Sqlstr = "select count(*) from " + DataBase + ".dbo.sysobjects where type='u' and [name]='" + Table + "'";
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                //int n = cmd.ExecuteNonQuery();
                //int n = int.Parse(cmd.ExecuteScalar().ToString());
                int n = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
                if (n > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        #region 执行sql字符串
        /// <summary>  
        /// 执行不带参数的SQL语句  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public int ExecuteSql(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
        }
        /// <summary>  
        /// 执行带参数的SQL语句  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <param name="param">参数对象数组</param>  
        /// <returns></returns>  
        public int ExecuteSql(String Sqlstr, SqlParameter[] param)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                cmd.Parameters.AddRange(param);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
        }
        public int ExecuteSql(String Sqlstr, String ConnStr)
        {
            //String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return 1;
            }
        }
        /// <summary>  
        /// 返回DataReader  
        /// </summary>  
        /// <param name="Sqlstr"></param>  
        /// <returns></returns>  
        public SqlDataReader ExecuteReader(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的  
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = Sqlstr;
                conn.Open();
                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection  
            }
            catch //(Exception ex)  
            {
                return null;
            }
        }
        /// <summary>  
        /// 执行SQL语句并返回数据表  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public DataTable ExecuteDt(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>  
        /// 执行SQL语句并返回DataSet  
        /// </summary>  
        /// <param name="Sqlstr">SQL语句</param>  
        /// <returns></returns>  
        public DataSet ExecuteDs(String Sqlstr)
        {
            String ConnStr = GetSqlConnection();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }
        #endregion

        #region 执行sql字符串A

        public DataSet ReaderSqlDs(String Sqlstr, String ConnStr)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Open();
                da.Fill(ds);
                conn.Close();
                return ds;
            }
        }

        public DataTable ReaderSqlDt(String Sqlstr, String ConnStr)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }

        //读取SQL
        public string ReaderSql(string Sqlstr, string ConnStr)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                using (SqlCommand cmd = new SqlCommand(Sqlstr, conn))
                {
                    try
                    {
                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        reader.Read();
                        string str = reader[0].ToString();
                        conn.Close();
                        return str;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        conn.Close();
                        throw new Exception(E.Message);
                    }
                }
            }

        }

        #endregion

        // 取最大的ID  
        public Int32 GetMaxID(string SQLString)
        {
            DataTable dt = ExecuteDt(SQLString);
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }

            return 0;
        }
        /// <summary>
        /// 获取一个字符串
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public string GetString(string SQLString)
        {
            DataTable dt = ExecuteDt(SQLString);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }


        #region 操作存储过程
        /// <summary>  
        /// 运行存储过程(已重载)  
        /// </summary>  
        /// <param name="procName">存储过程的名字</param>  
        /// <returns>存储过程的返回值</returns>  
        public int RunProc(string procName)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            cmd.ExecuteNonQuery();
            this.Close();
            return (int)cmd.Parameters["ReturnValue"].Value;
        }
        /// <summary>  
        /// 运行存储过程(已重载)  
        /// </summary>  
        /// <param name="procName">存储过程的名字</param>  
        /// <param name="prams">存储过程的输入参数列表</param>  
        /// <returns>存储过程的返回值</returns>  
        public int RunProc(string procName, SqlParameter[] prams)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            cmd.ExecuteNonQuery();
            this.Close();
            return (int)cmd.Parameters[0].Value;
        }
        /// <summary>  
        /// 运行存储过程(已重载)  
        /// </summary>  
        /// <param name="procName">存储过程的名字</param>  
        /// <param name="dataReader">结果集</param>  
        public void RunProc(string procName, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, null);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
        /// <summary>  
        /// 运行存储过程(已重载)  
        /// </summary>  
        /// <param name="procName">存储过程的名字</param>  
        /// <param name="prams">存储过程的输入参数列表</param>  
        /// <param name="dataReader">结果集</param>  
        public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
        {
            SqlCommand cmd = CreateCommand(procName, prams);
            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
        /// <summary>  
        /// 创建Command对象用于访问存储过程  
        /// </summary>  
        /// <param name="procName">存储过程的名字</param>  
        /// <param name="prams">存储过程的输入参数列表</param>  
        /// <returns>Command对象</returns>  
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            // 确定连接是打开的  
            Open();
            //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );  
            SqlCommand cmd = new SqlCommand(procName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            // 添加存储过程的输入参数列表  
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                    cmd.Parameters.Add(parameter);
            }
            // 返回Command对象  
            return cmd;
        }
        /// <summary>  
        /// 创建输入参数  
        /// </summary>  
        /// <param name="ParamName">参数名</param>  
        /// <param name="DbType">参数类型</param>  
        /// <param name="Size">参数大小</param>  
        /// <param name="Value">参数值</param>  
        /// <returns>新参数对象</returns>  
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }
        /// <summary>  
        /// 创建输出参数  
        /// </summary>  
        /// <param name="ParamName">参数名</param>  
        /// <param name="DbType">参数类型</param>  
        /// <param name="Size">参数大小</param>  
        /// <returns>新参数对象</returns>  
        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }
        /// <summary>  
        /// 创建存储过程参数  
        /// </summary>  
        /// <param name="ParamName">参数名</param>  
        /// <param name="DbType">参数类型</param>  
        /// <param name="Size">参数大小</param>  
        /// <param name="Direction">参数的方向(输入/输出)</param>  
        /// <param name="Value">参数值</param>  
        /// <returns>新参数对象</returns>  
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size > 0)
            {
                param = new SqlParameter(ParamName, DbType, Size);
            }
            else
            {
                param = new SqlParameter(ParamName, DbType);
            }
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }
            return param;
        }
        #endregion


        #region 数据库连接和关闭
        /// <summary>  
        /// 打开连接池  
        /// </summary>  
        private void Open()
        {
            // 打开连接池  
            if (con == null)
            {
                //这里不仅需要using System.Configuration;还要在引用目录里添加  
                con = new SqlConnection(GetSqlConnection());
                con.Open();
            }
        }
        /// <summary>  
        /// 关闭连接池  
        /// </summary>  
        public void Close()
        {
            if (con != null)
                con.Close();
        }
        /// <summary>  
        /// 释放连接池  
        /// </summary>  
        public void Dispose()
        {
            // 确定连接已关闭  
            if (con != null)
            {
                con.Dispose();
                con = null;
            }
        }
        #endregion
    }
}

/***   
简单用一下:

view plaincopy to clipboardprint?
using System;   
using System.Collections.Generic;   
using System.Text;   
using System.Data;   
using System.Data.SqlClient;   
using System.Collections;   
using HelloWinForm.DBUtility;   
namespace HelloWinForm.DAL   
{   
    class Student   
    {   
        public string test()   
        {   
            string str = "";   
            SqlDataReader dr = SQLHelper.ExecuteReader("select * from Student");   
            while (dr.Read())   
            {   
                str += dr["StudentNO"].ToString();   
            }   
            dr.Close();   
            return str;   
        }   
    }   
}   
**/